package database;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map;

public class Flashcard {

	private Connection connection = null;

	public Flashcard(Connection connection) {
		this.connection = connection;
		if (this.connection.equals(null)) {
			System.out.println("Connection is not ready when creating class Flashcard");
		}

	}
	public String getCardNum(String bookName) {
		String query1 = "SELECT COUNT( * ) FROM chen869.flashcard WHERE Book=";
		String query = query1 + "'" + bookName + "'";
		String num = "empty";
		try {
			Statement st = connection.createStatement();
			ResultSet rs = st.executeQuery(query);
			while (rs.next()) {
				num = rs.getString(1);
				//System.out.println("num:"+num);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			System.out.println("select has gone wrong");
			return null;
		}
		return num;
	}
	
	public Map<String,String> getAllCards(String name) {
		String query1 = "SELECT Front FROM chen869.flashcard WHERE Book=";
		String query = query1 + "'" + name + "'";
		Map<String,String> map = new HashMap<String,String>();
		try {
			Statement st = connection.createStatement();
			ResultSet rs = st.executeQuery(query);
			while (rs.next()) {
				String front = rs.getString("Front");
				map.put(front,selectBack(front, name));
			}
		} catch (SQLException e) {
			System.out.println("selectFront has gone wrong");
			return null;
		}
		return map;
	}
	
	public ArrayList<String> selectFront(String name) {
		String query1 = "SELECT Front FROM chen869.flashcard WHERE Book=";
		String query = query1 + "'" + name + "'";
		ArrayList<String> front = new ArrayList<String>();
		try {
			Statement st = connection.createStatement();
			ResultSet rs = st.executeQuery(query);
			while (rs.next()) {
				front.add(rs.getString("Front"));
			}
		} catch (SQLException e) {
			System.out.println("selectFront has gone wrong");
			return null;
		}
		return front;
	}

	public String selectBack(String front, String name) {
		String query1 = "SELECT Back FROM chen869.flashcard WHERE Front=";
		String query = query1 + "'" + front + "'" + "AND Book='" + name + "'";
		// System.out.println(query);
		String back = null;
		try {
			Statement st = connection.createStatement();
			ResultSet rs = st.executeQuery(query);
			while (rs.next()) {
				back = rs.getString("Back");
			}
		} catch (SQLException e) {
			System.out.println("selectBack has gone wrong");
			return null;
		}
		return back;
	}

	public void insertCard(String bookName, String front, String back) {

		String query = "INSERT INTO chen869.flashcard (Front, Back, Book) VALUES (\'"+front+"\',\'"+back+"\',\'"+bookName+"\')";
		try{
			Statement st = connection.createStatement();
			st.executeUpdate(query);
		}catch(SQLException e){
			System.out.println("insertCard has gone wrong");
		}
			
		System.out.println(query);
		return;
	}

	public void deleteCard(String front, String name) {
		String query = "DELETE FROM chen869.flashcard WHERE Front = '" + front + "' AND Book='" + name
				+ "'";
		try {
			Statement st = connection.createStatement();
			st.executeUpdate(query);
		} catch (SQLException e) {
			System.out.println("Flash Card deletion has gone wrong");
		}

		System.out.println(query);
		return;
	}

	// public static void main(String[] argv) {
	// Flashcard test = new Flashcard();
	// ArrayList<String> haha = test.selectFront("cs252");
	// String hehe = test.selectBack("front2","cs141");
	// System.out.println(hehe);
	// //System.out.println(haha+" "+hehe);
	// test.insertCard("5","eee", "fff","cs307");
	// //test.deleteCard("front2","cs252");
	// }
}